package uk.gov.swmprobation.cplite;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	private static DatabaseHandler mInstance = null;
	
    // Database Version
    private static final int DATABASE_VERSION = 3;
 
    // Database Name
    private static final String DATABASE_NAME = "projectsManager";
 
    // Table name
    private static final String TABLE_PROJECTS = "projects";
 
    // Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_IMAGE_PATH = "image_path";
    private static final String KEY_PROJ_NAME = "project_name";
    private static final String KEY_PROJ_STATUS = "project_status";
    private static final String KEY_PROJ_TYPE = "project_type";
    private static final String KEY_PROJ_ID = "project_id";
    private static final String KEY_LAT = "lat";
    private static final String KEY_LNG = "lng";
    private static final String KEY_OLD_LAT = "oldlat";
    private static final String KEY_OLD_LNG = "oldlng";
    private static final String KEY_UPDATE = "updated";
    private static final String KEY_SUBSCRIBED = "subscribed";
    private static final String KEY_COMMENT = "comment";
    private static final String KEY_TRUST = "trust";
    
    
    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
      //  this.mContext = context;
    }
 
    public static DatabaseHandler getInstance(Context ctx) {
        if (mInstance == null) {
            mInstance = new DatabaseHandler(ctx.getApplicationContext());
        }
        return mInstance;
    }
    
    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {

        String CREATE_PROJECTS_TABLE = "CREATE TABLE " + TABLE_PROJECTS + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_IMAGE_PATH + " TEXT,"
                + KEY_PROJ_NAME + " TEXT," + KEY_PROJ_STATUS + " TEXT,"
                + KEY_PROJ_TYPE + " INTEGER,"+ KEY_PROJ_ID + " TEXT,"+ KEY_LAT + " REAL," 
                + KEY_LNG + " REAL," + KEY_UPDATE + " TEXT," + KEY_SUBSCRIBED + " TEXT,"+ KEY_COMMENT+ " TEXT,"
                + KEY_OLD_LAT + " REAL," + KEY_OLD_LNG + " REAL,"+ KEY_TRUST + " TEXT"
                +")";

        db.execSQL(CREATE_PROJECTS_TABLE);
    }
 
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    	
    		    if (oldVersion == 2){
    		    	String upgradeQuery = "ALTER TABLE "+TABLE_PROJECTS+" ADD COLUMN "+KEY_TRUST+" TEXT;";
    		        db.execSQL(upgradeQuery);
    		    }
    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */
    
    static // Adding new project
    void addProject(Project project) {
        SQLiteDatabase db = mInstance.getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put(KEY_IMAGE_PATH, project.getImagePath());
        values.put(KEY_PROJ_NAME, project.getProjectName());
        values.put(KEY_PROJ_STATUS, project.getProjectStatus());
        values.put(KEY_PROJ_TYPE, project.getProjectType());
        values.put(KEY_PROJ_ID, project.getProjectId());
        values.put(KEY_LAT, project.getLat());
        values.put(KEY_LNG, project.getLng());
        values.put(KEY_UPDATE, String.valueOf(project.getUpdated()));
        values.put(KEY_SUBSCRIBED, String.valueOf(project.getSubscribed()));
        values.put(KEY_COMMENT, String.valueOf(project.getProjectComment()));
        values.put(KEY_OLD_LAT, project.getOldLat());
        values.put(KEY_OLD_LNG, project.getOldLng());
        values.put(KEY_TRUST, project.getTrust());
       
        // Inserting Row
        db.insert(TABLE_PROJECTS, null, values);

        db.close();
    }
 
    // Getting single project
    static Project getProject(int id) {
        SQLiteDatabase db = mInstance.getReadableDatabase();
        Cursor cursor = db.query(TABLE_PROJECTS, new String[] { KEY_ID,
        		KEY_IMAGE_PATH, KEY_PROJ_NAME,KEY_PROJ_STATUS,KEY_PROJ_TYPE,KEY_PROJ_ID,KEY_LAT,KEY_LNG,KEY_UPDATE,KEY_SUBSCRIBED,KEY_COMMENT,KEY_OLD_LAT,KEY_OLD_LNG,KEY_TRUST}, KEY_ID + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        Project project = new Project();
        if (cursor != null & cursor.moveToFirst()){
        	project = new Project(Integer.parseInt(cursor.getString(0)),
                cursor.getString(1), cursor.getString(2), cursor.getString(3), Integer.parseInt(cursor.getString(4)), Integer.parseInt(cursor.getString(5)), Double.parseDouble(cursor.getString(6)), Double.parseDouble(cursor.getString(7)),Boolean.parseBoolean(cursor.getString(8)),Boolean.parseBoolean(cursor.getString(9)), cursor.getString(10), Double.parseDouble(cursor.getString(11)), Double.parseDouble(cursor.getString(12)),cursor.getString(13));
        }
        db.close();
        return project;
    }
 
    // Getting All Projects
    public List<Project> getAllProjects() {
        List<Project> projectList = new ArrayList<Project>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_PROJECTS;
 
        SQLiteDatabase db = mInstance.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
 
        // looping through all rows and adding to list
        if( cursor != null && cursor.moveToFirst() ){
            do {
                Project project = new Project();
                project.setId(Integer.parseInt(cursor.getString(0)));
                project.setImagePath(cursor.getString(1));
                project.setProjectName(cursor.getString(2));
                project.setProjectStatus(cursor.getString(3));
                project.setProjectType(cursor.getInt(4));
                project.setProjectId(cursor.getInt(5));
                project.setLat(cursor.getDouble(6));
                project.setLng(cursor.getDouble(7));
                project.setUpdated(Boolean.parseBoolean(cursor.getString(8)));
                project.setSubscribed(Boolean.parseBoolean(cursor.getString(9)));
                project.setProjectComment(cursor.getString(10));
                project.setOldLat(cursor.getDouble(11));
                project.setOldLng(cursor.getDouble(12));
                project.setTrust(cursor.getString(13));
                // Adding project to list
                projectList.add(project);
            } while (cursor.moveToNext());
        }
        db.close();
        return projectList;
    }
    
 // Getting Projects by type
    public static List<Project> getTypeProjects(int type) {
        List<Project> projectList = new ArrayList<Project>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_PROJECTS + " WHERE " + KEY_PROJ_TYPE +"="+ type;
 
        SQLiteDatabase db = mInstance.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
 
        // looping through all rows and adding to list
        if( cursor != null && cursor.moveToFirst() ){
            do {
                Project project = new Project();
                project.setId(Integer.parseInt(cursor.getString(0)));
                project.setImagePath(cursor.getString(1));
                project.setProjectName(cursor.getString(2));
                project.setProjectStatus(cursor.getString(3));
                project.setProjectType(cursor.getInt(4));
                project.setProjectId(cursor.getInt(5));
                project.setLat(cursor.getDouble(6));
                project.setLng(cursor.getDouble(7));
                project.setUpdated(Boolean.parseBoolean(cursor.getString(8)));
                project.setSubscribed(Boolean.parseBoolean(cursor.getString(9)));
                project.setProjectComment(cursor.getString(10));
                project.setOldLat(cursor.getDouble(11));
                project.setOldLng(cursor.getDouble(12));
                project.setTrust(cursor.getString(13));
                // Adding project to list
                projectList.add(project);
            } while (cursor.moveToNext());
        }
        db.close();
        return projectList;
    }
    
    // Updating single project
    public static void updateProject(Project project) {
        SQLiteDatabase db = mInstance.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_IMAGE_PATH, project.getImagePath());
        values.put(KEY_PROJ_NAME, project.getProjectName());
        values.put(KEY_PROJ_STATUS, project.getProjectStatus());
        values.put(KEY_PROJ_TYPE, project.getProjectType());
        values.put(KEY_PROJ_ID, project.getProjectId());
        values.put(KEY_LAT, project.getLat());
        values.put(KEY_LNG, project.getLng());
        values.put(KEY_UPDATE, String.valueOf(project.getUpdated()));
        values.put(KEY_SUBSCRIBED, String.valueOf(project.getSubscribed()));
        values.put(KEY_COMMENT, String.valueOf(project.getProjectComment()));
        values.put(KEY_OLD_LAT, project.getOldLat());
        values.put(KEY_OLD_LNG, project.getOldLng());
        values.put(KEY_TRUST, project.getTrust());
 
        // updating row
        db.update(TABLE_PROJECTS, values, KEY_ID + " = ?",
                new String[] { String.valueOf(project.getId()) });
        
        db.close();
    }
 
    // Deleting single project
    public static void deleteProject(int id) {
    	PhotoTools.deleteThumbnail(id);
    	PhotoTools.deletePhoto(id);
        SQLiteDatabase db = mInstance.getWritableDatabase();
        db.delete(TABLE_PROJECTS, KEY_ID + " = ?",
                new String[] { String.valueOf(id) });
        db.close();
    }
 
    // Getting projects Count
    public static int getProjectsCount() {
        String countQuery = "SELECT  * FROM " + TABLE_PROJECTS;
        SQLiteDatabase db = mInstance.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int count = 0;
        if( cursor != null ){
        	count = cursor.getCount();
        }
        cursor.close();
        db.close();
 
        // return count
        return count;
    }
    
 // Getting projects Count for type
    public static int getProjectsCountType(int type) {
        String countQuery = "SELECT  * FROM " + TABLE_PROJECTS+ " WHERE "+ KEY_PROJ_TYPE +"="+ type;
        SQLiteDatabase db = mInstance.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int count = 0;
        if( cursor != null ){
        	count = cursor.getCount();
        }
        cursor.close();
        db.close();
 
        // return count
        return count;
    }
    
 // Getting projects Types
    public static List<Integer> getProjectTypes() {
        String countQuery = "SELECT "+KEY_PROJ_TYPE+" FROM " + TABLE_PROJECTS + " GROUP BY "+KEY_PROJ_TYPE;
        SQLiteDatabase db = mInstance.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        List<Integer> types = new ArrayList<Integer>();
        if( cursor != null && cursor.moveToFirst() ){
            do {
            	types.add(cursor.getInt(0));
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
 
        // return count
        return types;
    }
    
 // Get highest id
    public static int getHighestId() {
        String countId = "SELECT MAX("+KEY_ID+") FROM " + TABLE_PROJECTS;
        SQLiteDatabase db = mInstance.getReadableDatabase();
        Cursor cursor = db.rawQuery(countId, null);
        int id = 1;
        if( cursor != null && cursor.moveToFirst() ){
            if(cursor.getString(0)!=null){
            	id = Integer.parseInt(cursor.getString(0));
            }
        }
        cursor.close();
        db.close();
 
        // return count
        return id;
    }
    
//Get local id from project id
    public static int getLocalId(int projectId){
    	int id = 0;
    	String findId = "SELECT "+KEY_ID+" FROM " + TABLE_PROJECTS + " WHERE "+KEY_PROJ_ID+ "="+projectId;
        SQLiteDatabase db = mInstance.getReadableDatabase();
        Cursor cursor = db.rawQuery(findId, null);
        if( cursor != null && cursor.moveToFirst() ){
            if(cursor.getString(0)!=null){
            	id = Integer.parseInt(cursor.getString(0));
            }
        }
        cursor.close();
        db.close();
    	return id;
    }

//Get waiting to send projects
	public static List<Integer> getWaitingIds(){
		String findWaiting = "SELECT "+KEY_ID+" FROM " + TABLE_PROJECTS + " WHERE "+KEY_PROJ_STATUS+ "='Waiting'";
		SQLiteDatabase db = mInstance.getReadableDatabase();
		Cursor cursor = db.rawQuery(findWaiting, null);
		 
		List<Integer> waitingIds = new ArrayList<Integer>();
		if( cursor != null && cursor.moveToFirst() ){
            do {
            	waitingIds.add(cursor.getInt(0));
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return waitingIds;
	}

	//Get Unsubscribed
	public static List<List<Integer>> getUnsubscribed(){
		String findUnsubscribed = "SELECT "+KEY_PROJ_ID+","+KEY_PROJ_TYPE+" FROM " + TABLE_PROJECTS + " WHERE "+KEY_SUBSCRIBED+ "='false' AND "+KEY_PROJ_TYPE+"!='"+Project.TYPE_DRAFT+"'";
		SQLiteDatabase db = mInstance.getReadableDatabase();
		Cursor cursor = db.rawQuery(findUnsubscribed, null);
		List<List<Integer>> unsubscribed = new ArrayList<List<Integer>>();
//		if (cursor == null){
//			return null;
//		}
		if( cursor != null && cursor.moveToFirst() ){
            do {
            	List<Integer> listItem = new ArrayList<Integer>();
            	listItem.add(cursor.getInt(0));
            	listItem.add(cursor.getInt(1));
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return unsubscribed;
	}
	
	//Subscribe Projects
	public static void confirmSubscribed(List<List<Integer>> unsubscribed){
		int n =unsubscribed.size();
		for(int i = 0;i<n;i++){
			int localId = getLocalId(Integer.valueOf(unsubscribed.get(i).get(0)));
			Project project = getProject(localId);
			project.setSubscribed(true);
			updateProject(project);
		}
	}
	
	//Set Updated Flag
	public static void flagUpdated(int projectid,String status){
		int localId = getLocalId(Integer.valueOf(projectid));
		Project project = getProject(localId);
		project.setProjectStatus(status);
		project.setUpdated(true);
		updateProject(project);
	}
	
	//Get name from Project Id
	public static String projectName(String projectid){
		int localId = getLocalId(Integer.valueOf(projectid));
		Project project = getProject(localId);
		return project.getProjectName();
	}

}